package com.cgt.web.dao.impl;

import com.cgt.web.dao.TeaDao;
import com.cgt.web.pojo.Teacher;
import com.cgt.web.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeaDaoImpl implements TeaDao {

    @Override
    public List<Teacher> selectAll() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Teacher> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id,name,age,address FROM teacher";
            statement = connection.prepareStatement(sql);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String address = resultSet.getString("address");
                Teacher teacher = new Teacher(id,name,age,address);
                list.add(teacher);
            }
            for (Teacher teacher : list) {
                System.out.println(teacher);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }   finally {
            JDBCUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    @Override
    public void deleteById(String id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "DELETE FROM teacher WHERE id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, Integer.parseInt(id));
            System.out.println(statement);
            int count = statement.executeUpdate();
            System.out.println("count: " + count);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(connection,statement,null);
        }
    }

    @Override
    public void addt(Teacher teacher) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection =JDBCUtil.getConnection();
            String sql = "INSERT INTO teacher(name,age,address) VALUES(?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,teacher.getName());
            statement.setInt(2, teacher.getAge());
            statement.setString(3,teacher.getAddress());
            System.out.println(statement);
            int count = statement.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(connection,statement,null);
        }
    }

    @Override
    public Teacher toUpdate(String id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Teacher teacher = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT name,age,address FROM teacher WHERE id = ?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1, Integer.parseInt(id));
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String address = resultSet.getString("address");
                teacher = new Teacher(Integer.parseInt(id),name,age,address);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(connection,statement,resultSet);
        }
        return teacher;
    }

    @Override
    public void update(Teacher teacher) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE teacher SET name=?,age=?,address=? WHERE id =?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,teacher.getName());
            statement.setInt(2, teacher.getAge());
            statement.setString(3,teacher.getAddress());
            statement.setInt(4, teacher.getId());
            System.out.println(statement);
            int count = statement.executeUpdate();
            System.out.println("count: " + count);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,statement,null);
        }
    }

    @Override
    public List<Teacher> selectByPage(int offset, int pageSize) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Teacher> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id,name,age,address from teacher LIMIT ?,?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,offset);
            statement.setInt(2,pageSize);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String address = resultSet.getString("address");
                Teacher teacher = new Teacher(id,name,age,address);
                list.add(teacher);
            }
            for (Teacher teacher : list) {
                System.out.println(teacher);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    @Override
    public int getTotalCount() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int totalCount = 0;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT count(*) FROM teacher";
            statement=connection.prepareStatement(sql);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(connection,statement,resultSet);
        }
        return totalCount;
    }
}
